package com.simtop.util;

import com.alibaba.fastjson.JSON;
import com.simtop.pojo.YongdaoPo;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ReadExcelUtil {
    /**
     * 读取excel文件
     * @param dir  文件目录
     * @param file   文件名
     * @param sheetName  Sheet名
     * @return
     */
    public static List readExcel(String dir, String file, String sheetName) {
        List resultList = new ArrayList<YongdaoPo>();
        if (file.endsWith(".xlsx")) {// 根据表末尾扩展名判断使用哪个方法读取
            try {
                XSSFWorkbook book = null;
                try {
                    book = new XSSFWorkbook(new File(dir + File.separator + file));
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (InvalidFormatException e) {
                    e.printStackTrace();
                }
                XSSFSheet sheet = book.getSheet(sheetName);
                if (sheet.getLastRowNum() != 0) {
                    XSSFRow row;
                    //默认第一行为标题行，index = 0
                    XSSFRow titleRow = sheet.getRow(0);
                    for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                        row = sheet.getRow(i);
                        Map resultMap = new HashMap();
                        YongdaoPo yongdaoPo = new YongdaoPo();
                        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                            //循环取每个单元格(cell)的数据
                            XSSFCell titleCell = titleRow.getCell(j);
                            XSSFCell xssfCell = row.getCell(j);
                            resultMap.put(getString(titleCell), getString(xssfCell));
                            yongdaoPo =MapToObjectUtil.mapToObj(resultMap,YongdaoPo.class);
                        }
                        resultList.add(yongdaoPo);
                    }
                }
                book.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }else {//处理.xls文件
            try {
                HSSFWorkbook book = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(new File(dir + File.separator + file))));
                HSSFSheet sheet = book.getSheet(sheetName);
                if (sheet.getLastRowNum() != 0) {
                    Row row;
                    //默认第一行为标题行，index = 0
                    Row titleRow = sheet.getRow(0);
                    for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                        row = sheet.getRow(i);
                        Map resultMap = new HashMap();
                        YongdaoPo yongdaoPo = new YongdaoPo();
                        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                            //循环取每个单元格(cell)的数据
                            Cell titleCell = titleRow.getCell(j);
                            Cell xssfCell = row.getCell(j);
                            resultMap.put(getString(titleCell), getString(xssfCell));
                            yongdaoPo =MapToObjectUtil.mapToObj(resultMap,YongdaoPo.class);
                        }
                        resultList.add(yongdaoPo);
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
            return resultList;
    }

        /**
         * 把单元格的内容转为字符串
         * @param xssfCell 单元格
         * @return 字符串
         */
        public static String getString(XSSFCell xssfCell){
            if (xssfCell == null) {
                return "";
            }
            if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
                return String.valueOf(xssfCell.getNumericCellValue());
            } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
                return String.valueOf(xssfCell.getBooleanCellValue());
            } else {
                return xssfCell.getStringCellValue();
            }
        }

    /**
     * 把单元格的内容转为字符串
     * @param xssfCell 单元格
     * @return 字符串
     */
    public static String getString(Cell xssfCell){
        if (xssfCell == null) {
            return "";
        }
        if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else {
            return xssfCell.getStringCellValue();
        }
    }


    public static void main(String args[]) {
        String dir = "C:\\Users\\kangcoo_sn\\Desktop\\";
        List result = ReadExcelUtil.readExcel(dir, "泳道01.xlsx", "Sheet1");
        for (int i = 0; i < result.size(); i++) {
            System.out.println(JSON.toJSONString(result.get(i)));
        }
    }

}

